﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace 期末考试
{
    class sqlDA
    {
        SqlConnection _sqlConnection;
        SqlCommand _sqlCommand;

        [Obsolete]
        public sqlDA()
        {
            _sqlConnection = new SqlConnection("server=.;database=Blogs;user=sa;pwd=123");

            void addData(sqlData data)
            {

                //构建表结构
                //listView1();
                //listView1.Columns.Add("用户名");
                //.Columns.Add("密码");
                ////将集合中的数据放入listview1中
                //foreach (SqlData data in list)
                //{
                //  ListViewItem it = new ListViewItem();//构建一个listviewitem对象,通过它把数据放入listview1中
                //  it.Text = data.Code;
                // it.SubItems.Add(data.Name);
                //it.SubItems.Add(data.Pwd);

                // listView1.Items.Add(it);//将数据放入listview1的items集合中
            }
        }

        internal void addData(sqlData udata)
        {
            throw new NotImplementedException();
        }

        void select()
        {
            List<sqlData> list = new List<sqlData>();
            _sqlCommand = new SqlCommand();
            _sqlConnection.Open();
            _sqlCommand.CommandText = "select * from users";
            SqlDataReader dr = _sqlCommand.ExecuteReader();

            while (dr.Read())
            {
                sqlData data = new sqlData();
                data.Id = dr["Id"].ToString();
                data.username = dr["username"].ToString();
                data.password = dr["password"].ToString();
                list.Add(data);
            }
        }
        List<sqlData> listView1(string username)
        {
            List<sqlData> list = new List<sqlData>();
            _sqlCommand = _sqlConnection.CreateCommand();
            _sqlCommand.CommandText = "select * from users where username like '%+uname+%'.Tolist()";
            SqlDataReader dr = _sqlCommand.ExecuteReader();

            while (dr.Read())
            {
                sqlData data = new sqlData();
                data.Id = dr["Id"].ToString();
                data.username = dr["username"].ToString();
                data.password = dr["password"].ToString();
                list.Add(data);

            }
            _sqlConnection.Close();
            return list;
        }
        void update(sqlData data)
        {
            _sqlConnection.Open();
            _sqlCommand = _sqlConnection.CreateCommand();
            _sqlCommand.CommandText = "update users set username = @username,password=@password where Id=@Id.Tolist()";


            _sqlCommand.Parameters.Add("@username", data.username);
            _sqlCommand.Parameters.Add("@password", data.password);
            _sqlCommand.Parameters.Add("@Id", data.Id);

            _sqlCommand.ExecuteNonQuery();
            _sqlConnection.Close();
        }



        [Obsolete]
        private void delete(sqlData data)
        {
            _sqlConnection.Open();
            _sqlCommand = _sqlConnection.CreateCommand();
            _sqlCommand.CommandText = "delete from users where Id=@Id";
            _ = _sqlCommand.Parameters.Add("@Id", data.Id);
            _sqlCommand.ExecuteNonQuery();
            _sqlConnection.Close();
        }

        /*internal void addData(sqlData udata)
        {
            throw new NotImplementedException();
        }*/

        public void bindData(List<sqlData> list)
        {
            
            //listView1.Columns.Add("Id");
            //listView1.Columns.Add("用户名");
            //listView1.Columns.Add("密码");
            //foreach (SqlData data in list)
            //{
            // ListViewItem it = new ListViewItem();
            //it.Text = data.Code;
            //it.SubItems.Add(data.Name);
            //it.SubItems.Add(data.Pwd);

            // listView1.Items.Add(it);
            // }
        }
    }
}
